package com.yuyou.fn.controller;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.github.wens.mybatisplus.plugins.Page;
import com.google.common.base.Predicate;
import com.google.common.collect.Collections2;
import com.google.common.collect.Lists;
import com.yuyou.fn.common.util.BeanUtils;
import com.yuyou.fn.common.util.BigDecimals;
import com.yuyou.fn.common.util.Utils;
import com.yuyou.fn.educational.entity.Campus;
import com.yuyou.fn.educational.service.ICampusService;
import com.yuyou.fn.educational.vo.StudentRegQueryVo2;
import com.yuyou.fn.electronicinvoice.service.IInvoiceService;
import com.yuyou.fn.electronicinvoice.vo.StudentClassInvoiceVo;
import com.yuyou.fn.electronicinvoice.vo.StudentInvoiceQueryVo;
import com.yuyou.fn.platform.permission.DataPermission;
import com.yuyou.fn.report.entity.Charge;
import com.yuyou.fn.report.entity.StudentRegDetail;
import com.yuyou.fn.report.service.IChargeService;
import com.yuyou.fn.report.service.IConsumeRecordService;
import com.yuyou.fn.report.service.IMergeStudentRegDetailService;
import com.yuyou.fn.report.service.IStudentRegDetailService;
import com.yuyou.fn.report.vo.*;
import com.yuyou.fn.settlement.entity.NewPayRecord;
import com.yuyou.fn.settlement.entity.PayRecord;
import com.yuyou.fn.settlement.service.INewPayRecordService;
import com.yuyou.fn.settlement.service.IPayRecordService;
import com.yuyou.fn.settlement.vo.PayRecordQueryVo;
import com.yuyou.fn.settlement.vo.PayWay;
import com.yuyou.fn.util.Webs;
import com.yuyou.fn.vo.ConsumeRecordVo;
import com.yuyou.fn.vo.PayRecordVo;
import io.swagger.annotations.ApiOperation;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.validation.Valid;
import java.io.IOException;
import java.math.BigDecimal;
import java.util.*;

/**
 * Created by wens on 2017/7/6.
 */
@Controller
@RequestMapping("/api/admin/report")
public class AdminReportApiController extends ApiBaseController {


    private static Map<String, String> busTypeMap = new HashMap<>();
    private static Map<String, String> payWayMap = new HashMap<>();
    private static Map<String, String> courseOrderChargingMap = new HashMap<>();
    private static Map<String, String> studentRefundStatusMap = new HashMap<>();
    private static Map<String, String> bookingChargingStatusMap = new HashMap<>();
    private static Map<String, String> refundWayMap = new HashMap<>();
    private static String[] totalTitles = new String[]{"收费校区", "合计", "现金", "刷卡", "余额", "微信", "支付宝"};
    private static String[] refundTitles = new String[]{"报名校区", "合计", "转账退费", "退账户", "现金"};
    private static String[] titles = new String[]{"业务类型", "订单号", "订单状态", "年份", "季节",
            "学生姓名", "学生编号", "就读年级", "就读学校", "支付方式", "应缴金额", "实缴金额",
            "优惠原因", "优惠金额", "实退金额", "退费方式", "报名校区",
            "收费校区", "操作人", "报名方式", "申请退费时间", "日结状态",
            "报读班级", "年级", "科目", "课程", "产品", "区域", "业务组", "备注", "时间", "支付渠道交易流水号"};

    static {

        busTypeMap.put("0", "报名收费");
        busTypeMap.put("2", "退班退费");
        busTypeMap.put("3", "众筹押金");
        busTypeMap.put("4", "众筹退费");
        busTypeMap.put("5", "账户提现");
        busTypeMap.put("6", "账户充值");


        payWayMap.put("0", "现金");
        payWayMap.put("1", "刷卡");
        payWayMap.put("2", "转账");
        payWayMap.put("3", "账户");
        payWayMap.put("4", "微信");
        payWayMap.put("5", "支付宝");
        payWayMap.put("6", "tms账户");


        courseOrderChargingMap.put("0", "未缴费");
        courseOrderChargingMap.put("1", "欠费");
        courseOrderChargingMap.put("2", "已缴费");


        studentRefundStatusMap.put("0", "处理中");
        studentRefundStatusMap.put("1", "已审核");
        studentRefundStatusMap.put("2", "已拒绝");


        bookingChargingStatusMap.put("0", "未缴费");
        bookingChargingStatusMap.put("1", "欠费");
        bookingChargingStatusMap.put("2", "已缴费");
        bookingChargingStatusMap.put("3", "-");
        bookingChargingStatusMap.put("4", "撤销");
        bookingChargingStatusMap.put("5", "退款");


        refundWayMap.put("0", "支付宝");
        refundWayMap.put("1", "微信");
        refundWayMap.put("2", "现金");
        refundWayMap.put("3", "退账户");
        refundWayMap.put("4", "转账退费");
    }


    @Resource
    private IChargeService chargeService;

    @Resource
    private IConsumeRecordService consumeRecordService;


    @Resource
    private INewPayRecordService newPayRecordService;

    @Resource
    private ICampusService campusService;

    @Resource
    private IInvoiceService invoiceService;

    @Resource
    private IStudentRegDetailService studentRegDetailService;

    @Resource
    private IMergeStudentRegDetailService mergeStudentRegDetailService;

    @ApiOperation("发票使用情况")
    @RequestMapping(value = "/findStudentClassInvoicePage", method = {RequestMethod.GET, RequestMethod.POST})
    @ResponseBody
    public ReturnResult<Page<StudentClassInvoiceVo>> findStudentClassInvoicePage(@ModelAttribute StudentInvoiceQueryVo studentInvoiceQueryVo, @RequestParam(value = "pageNo", defaultValue = "1") int pageNo,
                                                                                 @RequestParam(value = "pageSize", defaultValue = "20") int pageSize, HttpServletRequest request) {
        Page<StudentClassInvoiceVo> studentClassInvoiceVoPage = invoiceService.findStudentClassInvoicePage(studentInvoiceQueryVo, pageNo, pageSize);
        return ReturnResult.success(studentClassInvoiceVoPage);
    }

    @ApiOperation("导出发票使用情况")
    @RequestMapping(value = "/exportStudentClassInvoice", method = {RequestMethod.GET, RequestMethod.POST})
    @ResponseBody
    public ReturnResult<Void> exportStudentClassInvoice(@ModelAttribute StudentInvoiceQueryVo studentInvoiceQueryVo, HttpServletRequest request, HttpServletResponse response) throws IOException {

        Webs.writeExcel(request, response, invoiceService.exportStudentClassInvoice(studentInvoiceQueryVo), "发票使用情况");
        return ReturnResult.success("ok");
    }


    @ApiOperation("财务收支")
    @RequestMapping(value = "/chargeList", method = {RequestMethod.GET, RequestMethod.POST})
    @ResponseBody
    public ReturnResult<Page<Charge>> chargeList(ChargeQueryVo chargeQueryVo, @RequestParam(value = "pageNo", defaultValue = "1") int pageNo,
                                                 @RequestParam(value = "pageSize", defaultValue = "20") int pageSize, HttpServletRequest request) {
        DataPermission dataPermission = getDataPermission(request);
        Page<Charge> page = chargeService.findByQuery(chargeQueryVo, dataPermission, pageNo, pageSize);
        return ReturnResult.success(page);
    }


    @ApiOperation("校区财务收支")
    @RequestMapping(value = "/campusChargeList", method = {RequestMethod.GET, RequestMethod.POST})
    @ResponseBody
    public ReturnResult<List<Charge>> campusChargeList(ChargeQueryVo chargeQueryVo, HttpServletRequest request) {
        List<Charge> list = chargeService.findListByQuery(chargeQueryVo, getDataPermission(request));
        return ReturnResult.success(list);
    }

    @ApiOperation("更改日结状态")
    @RequestMapping(value = "/changeChargeDailyStatus", method = {RequestMethod.GET, RequestMethod.POST})
    @ResponseBody
    public ReturnResult<Void> changeChargeDailyStatus(@RequestParam("chargeIds[]") String[] chargeIds, @RequestParam("dailyStatus") Integer dailyStatus, HttpServletRequest request) {
        chargeService.changeDailyStatus(Arrays.asList(chargeIds), dailyStatus, getCurrentSysPrincipal());
        return ReturnResult.success("OK");
    }

    @ApiOperation("撤销日结状态")
    @RequestMapping(value = "/canelChargeDailyStatus", method = {RequestMethod.GET, RequestMethod.POST})
    @ResponseBody
    public ReturnResult<Void> canelChargeDailyStatus(@RequestParam("chargeIds[]") String[] chargeIds, @RequestParam(value = "dailyStatus", defaultValue = "0") Integer dailyStatus, HttpServletRequest request) {
        chargeService.cancelDailyStatus(Arrays.asList(chargeIds), dailyStatus, getCurrentSysPrincipal());
        return ReturnResult.success("OK");
    }


    @ApiOperation("导出财务收支")
    @RequestMapping(value = "/exportCharge", method = {RequestMethod.GET, RequestMethod.POST})
    public void exportCharge(ChargeQueryVo chargeQueryVo, HttpServletRequest request, HttpServletResponse response) throws IOException {

        DataPermission dataPermission = getDataPermission(request);
        Page<Charge> page = chargeService.findByQuery(chargeQueryVo, dataPermission, 1, 50000);

        SXSSFWorkbook excel = new SXSSFWorkbook();

        SXSSFSheet sheet = excel.createSheet("财务收支明细");
        SXSSFSheet totalSheet = excel.createSheet("报名收费汇总");

        SXSSFSheet totalRefundSheet = excel.createSheet("退班退费汇总");


        SXSSFRow firstRow = sheet.createRow(0);
        for (int i = 0; i < titles.length; i++) {

            SXSSFCell cell = firstRow.createCell(i);
            cell.setCellValue(titles[i]);
        }

        List<Charge> chargeList = page.getRecords();
        for (int i = 0; i < chargeList.size(); i++) {
            SXSSFRow row = sheet.createRow(i + 1);
            Charge charge = chargeList.get(i);
            writeRecordRow(row, charge);
        }
        writeSummaryForReg(totalSheet, chargeList);
        writeSummaryForRefund(totalRefundSheet, chargeList);
        Webs.writeExcel(request, response, excel, "财务收支");

    }


    @ApiOperation("导出校区财务收支")
    @RequestMapping(value = "/exportCampusCharge", method = {RequestMethod.GET, RequestMethod.POST})
    public void exportCampusCharge(ChargeQueryVo chargeQueryVo, HttpServletRequest request, HttpServletResponse response) throws IOException {

        DataPermission dataPermission = getDataPermission(request);
        Page<Charge> page = chargeService.findByQuery(chargeQueryVo, dataPermission, 1, 50000);

        SXSSFWorkbook excel = new SXSSFWorkbook();

        SXSSFSheet sheet = excel.createSheet("财务收支明细");
        SXSSFSheet totalSheet = excel.createSheet("报名收费汇总");
        SXSSFSheet refundTotalSheet = excel.createSheet("退班退费汇总");
        SXSSFRow firstRow = sheet.createRow(0);

        for (int i = 0; i < titles.length; i++) {
            SXSSFCell cell = firstRow.createCell(i);
            cell.setCellValue(titles[i]);

        }

        List<Charge> chargeList = page.getRecords();
        LinkedHashMap<Long, List<Charge>> orderMap = (LinkedHashMap) BeanUtils.toLinkGroup(chargeList, "orderId");
        int totalRowNum = 1;

        for (Map.Entry<Long, List<Charge>> entry : orderMap.entrySet()) {
            List<Charge> orderCharge = entry.getValue();

            BigDecimal tempReal = BigDecimal.ZERO;
            BigDecimal tempDiscount = BigDecimal.ZERO;
            BigDecimal tempRefund = BigDecimal.ZERO;
            for (int i = 0; i < orderCharge.size(); i++) {

                SXSSFRow row = sheet.createRow(totalRowNum++);
                Charge charge = orderCharge.get(i);
                writeRecordRow(row, charge);
                tempReal = tempReal.add(charge.getPaidAmount());
                tempDiscount = tempDiscount.add(charge.getDiscountAmount());
                tempRefund = tempRefund.add(charge.getRefundAmount());

            }
            SXSSFRow row = sheet.createRow(totalRowNum++);
            SXSSFCell cell = row.createCell(0);
            cell.setCellValue("实缴金额总数小计");

            cell = row.createCell(11);
            cell.setCellValue(tempReal.toString());

            cell = row.createCell(13);
            cell.setCellValue(tempDiscount.toString());

            cell = row.createCell(16);
            cell.setCellValue(tempRefund.toString());
        }

        writeSummaryForReg(totalSheet, chargeList);
        writeSummaryForRefund(refundTotalSheet, chargeList);
        Webs.writeExcel(request, response, excel, "校区收支");

    }

    private void writeSummaryForRefund(SXSSFSheet sheet, List<Charge> chargeList) {
        SXSSFRow firstTotalRow = sheet.createRow(0);
        for (int i = 0; i < refundTitles.length; i++) {

            SXSSFCell cell = firstTotalRow.createCell(i);
            cell.setCellValue(refundTitles[i]);
        }

        Collection<Charge> refundCharges = Collections2.filter(chargeList, new Predicate<Charge>() {
            @Override
            public boolean apply(Charge input) {
                return input.getBusType() == 2;
            }
        });
        //`refund_way` int(1) DEFAULT NULL COMMENT '退款方式：0-支付宝，1-微信，2-现金，3-虚拟余额账户 ，4-银行卡转账',
        Map<String, List<Charge>> campusName2Charges = BeanUtils.toGroup(new ArrayList<>(refundCharges), "regCampus");
        int rowNum = 1;
        RefundWayAmount refundWayAmountForTotal = new RefundWayAmount();
        for (Map.Entry<String, List<Charge>> entry : campusName2Charges.entrySet()) {
            RefundWayAmount refundWayAmount = new RefundWayAmount();
            for (Charge charge : entry.getValue()) {
                switch (charge.getRefundWay()) {
                    case 4:
                        refundWayAmount.setTransfer(refundWayAmount.getTransfer().add(charge.getRefundAmount()));
                        break;
                    case 3:
                        refundWayAmount.setRefundAccount(refundWayAmount.getRefundAccount().add(charge.getRefundAmount()));
                        break;
                    case 2:
                        refundWayAmount.setRefundCash(refundWayAmount.getRefundCash().add(charge.getRefundAmount()));
                        break;
                }
            }

            refundWayAmount.setTotal(refundWayAmount.getTransfer().add(refundWayAmount.getRefundAccount()).add(refundWayAmount.getRefundCash()));
            refundWayAmountForTotal.setTotal(refundWayAmountForTotal.getTotal().add(refundWayAmount.getTotal()));
            refundWayAmountForTotal.setTransfer(refundWayAmountForTotal.getTransfer().add(refundWayAmount.getTransfer()));
            refundWayAmountForTotal.setRefundAccount(refundWayAmountForTotal.getRefundAccount().add(refundWayAmount.getRefundAccount()));
            refundWayAmountForTotal.setRefundCash(refundWayAmountForTotal.getRefundCash().add(refundWayAmount.getRefundCash()));
            SXSSFRow row = sheet.createRow(rowNum++);

            int c = 0;
            SXSSFCell cell = row.createCell(c++);
            cell.setCellValue(entry.getKey());

            cell = row.createCell(c++);
            cell.setCellValue(BigDecimals.formatBigDecimal(refundWayAmount.getTotal(), "0.00"));

            cell = row.createCell(c++);
            cell.setCellValue(BigDecimals.formatBigDecimal(refundWayAmount.getTransfer(), "0.00"));

            cell = row.createCell(c++);
            cell.setCellValue(BigDecimals.formatBigDecimal(refundWayAmount.getRefundAccount(), "0.00"));

            cell = row.createCell(c++);
            cell.setCellValue(BigDecimals.formatBigDecimal(refundWayAmount.getRefundCash(), "0.00"));


        }

        int c = 0;
        SXSSFRow row = sheet.createRow(rowNum);

        SXSSFCell cell = row.createCell(c++);
        cell.setCellValue("总计");

        cell = row.createCell(c++);
        cell.setCellValue(BigDecimals.formatBigDecimal(refundWayAmountForTotal.getTotal(), "0.00"));

        cell = row.createCell(c++);
        cell.setCellValue(BigDecimals.formatBigDecimal(refundWayAmountForTotal.getTransfer(), "0.00"));

        cell = row.createCell(c++);
        cell.setCellValue(BigDecimals.formatBigDecimal(refundWayAmountForTotal.getRefundAccount(), "0.00"));

        cell = row.createCell(c++);
        cell.setCellValue(BigDecimals.formatBigDecimal(refundWayAmountForTotal.getRefundCash(), "0.00"));

    }


    private void writeSummaryForReg(SXSSFSheet sheet, List<Charge> chargeList) {

        SXSSFRow firstTotalRow = sheet.createRow(0);
        for (int i = 0; i < totalTitles.length; i++) {

            SXSSFCell cell = firstTotalRow.createCell(i);
            cell.setCellValue(totalTitles[i]);
        }

        Collection<Charge> regCharges = Collections2.filter(chargeList, new Predicate<Charge>() {
            @Override
            public boolean apply(Charge input) {
                return input.getBusType() == 0;
            }
        });


        Map<String, List<Charge>> campusName2Charges = BeanUtils.toGroup(new ArrayList<>(regCharges), "chargeCampus");
        int rowNum = 1;
        PayWayAmount payWayAmountFotTotal = new PayWayAmount();
        for (Map.Entry<String, List<Charge>> entry : campusName2Charges.entrySet()) {
            PayWayAmount payWayAmount = new PayWayAmount();
            for (Charge charge : entry.getValue()) {
                switch (charge.getPayWay()) {
                    case 0:
                        payWayAmount.setCash(payWayAmount.getCash().add(charge.getPaidAmount()));
                        break;
                    case 1:
                        payWayAmount.setCreditCard(payWayAmount.getCreditCard().add(charge.getPaidAmount()));
                        break;
                    case 3:
                        payWayAmount.setBalance(payWayAmount.getBalance().add(charge.getPaidAmount()));
                        break;
                    case 4:
                        payWayAmount.setWeChat(payWayAmount.getWeChat().add(charge.getPaidAmount()));
                        break;
                    case 5:
                        payWayAmount.setAlipay(payWayAmount.getAlipay().add(charge.getPaidAmount()));
                        break;
                }
            }

            payWayAmount.setTotal(payWayAmount.getCreditCard().add(payWayAmount.getBalance()).add(payWayAmount.getWeChat()).add(payWayAmount.getAlipay()).add(payWayAmount.getCash()));
            payWayAmountFotTotal.setTotal(payWayAmountFotTotal.getTotal().add(payWayAmount.getTotal()));
            payWayAmountFotTotal.setCreditCard(payWayAmountFotTotal.getCreditCard().add(payWayAmount.getCreditCard()));
            payWayAmountFotTotal.setBalance(payWayAmountFotTotal.getBalance().add(payWayAmount.getBalance()));
            payWayAmountFotTotal.setWeChat(payWayAmountFotTotal.getWeChat().add(payWayAmount.getWeChat()));
            payWayAmountFotTotal.setAlipay(payWayAmountFotTotal.getAlipay().add(payWayAmount.getAlipay()));
            payWayAmountFotTotal.setCash(payWayAmountFotTotal.getCash().add(payWayAmount.getCash()));

            SXSSFRow row = sheet.createRow(rowNum++);

            int c = 0;
            SXSSFCell cell = row.createCell(c++);
            cell.setCellValue(entry.getKey());
            cell = row.createCell(c++);
            cell.setCellValue(BigDecimals.formatBigDecimal(payWayAmount.getTotal(), "0.00"));

            cell = row.createCell(c++);
            cell.setCellValue(BigDecimals.formatBigDecimal(payWayAmount.getCash(), "0.00"));

            cell = row.createCell(c++);
            cell.setCellValue(BigDecimals.formatBigDecimal(payWayAmount.getCreditCard(), "0.00"));

            cell = row.createCell(c++);
            cell.setCellValue(BigDecimals.formatBigDecimal(payWayAmount.getBalance(), "0.00"));

            cell = row.createCell(c++);
            cell.setCellValue(BigDecimals.formatBigDecimal(payWayAmount.getWeChat(), "0.00"));

            cell = row.createCell(c++);
            cell.setCellValue(BigDecimals.formatBigDecimal(payWayAmount.getAlipay(), "0.00"));

        }

        int c = 0;
        SXSSFRow row = sheet.createRow(rowNum);

        SXSSFCell cell = row.createCell(c++);
        cell.setCellValue("总计");

        cell = row.createCell(c++);
        cell.setCellValue(BigDecimals.formatBigDecimal(payWayAmountFotTotal.getTotal(), "0.00"));

        cell = row.createCell(c++);
        cell.setCellValue(BigDecimals.formatBigDecimal(payWayAmountFotTotal.getCash(), "0.00"));

        cell = row.createCell(c++);
        cell.setCellValue(BigDecimals.formatBigDecimal(payWayAmountFotTotal.getCreditCard(), "0.00"));

        cell = row.createCell(c++);
        cell.setCellValue(BigDecimals.formatBigDecimal(payWayAmountFotTotal.getBalance(), "0.00"));

        cell = row.createCell(c++);
        cell.setCellValue(BigDecimals.formatBigDecimal(payWayAmountFotTotal.getWeChat(), "0.00"));

        cell = row.createCell(c++);
        cell.setCellValue(BigDecimals.formatBigDecimal(payWayAmountFotTotal.getAlipay(), "0.00"));
    }

    private void writeRecordRow(SXSSFRow row, Charge charge) {
        int c = 0;
        SXSSFCell cell = row.createCell(c++);
        cell.setCellValue(busTypeMap.get(charge.getBusType().toString()));

        cell = row.createCell(c++);
        cell.setCellType(CellType.STRING);
        cell.setCellValue(String.valueOf(charge.getOrderId()));

        cell = row.createCell(c++);
        if (charge.getBusType() == 0) {
            cell.setCellValue(courseOrderChargingMap.get(charge.getOrderStatus().toString()));
        } else if (charge.getBusType() == 2) {
            cell.setCellValue(studentRefundStatusMap.get(charge.getOrderStatus().toString()));
        } else if (charge.getBusType() == 3 || charge.getBusType() == 4) {
            cell.setCellValue(bookingChargingStatusMap.get(charge.getOrderStatus().toString()));
        } else if (charge.getBusType() == 6) {//充值
            cell.setCellValue("已支付");
        } else {
            cell.setCellValue("");
        }

        cell = row.createCell(c++);
        cell.setCellValue(charge.getYear());

        cell = row.createCell(c++);
        cell.setCellValue(charge.getSeason());

        cell = row.createCell(c++);
        cell.setCellValue(charge.getStudentName());

        cell = row.createCell(c++);
        cell.setCellValue(charge.getStudentNo());

        cell = row.createCell(c++);
        cell.setCellValue(charge.getStudentGradeName());

        cell = row.createCell(c++);
        cell.setCellValue(charge.getStudentSchool());

        cell = row.createCell(c++);
        if (charge.getBusType() == 0 || charge.getBusType() == 6) {
            cell.setCellValue(charge.getPayWay() == null ? StringUtils.EMPTY : payWayMap.get(charge.getPayWay().toString()));
        } else {
            cell.setCellValue(StringUtils.EMPTY);
        }

        cell = row.createCell(c++);

        cell.setCellType(CellType.NUMERIC);
        cell.setCellValue(Double.parseDouble(BigDecimals.formatBigDecimal(charge.getPayableAmount() == null ? BigDecimal.ZERO : charge.getPayableAmount(), "0.00")));

        cell = row.createCell(c++);
        cell.setCellType(CellType.NUMERIC);
        cell.setCellValue(Double.parseDouble(BigDecimals.formatBigDecimal(charge.getPaidAmount() == null ? BigDecimal.ZERO : charge.getPaidAmount(), "0.00")));

        cell = row.createCell(c++);
        cell.setCellValue(charge.getDiscountReason());

        cell = row.createCell(c++);

        cell.setCellValue(Double.parseDouble(BigDecimals.formatBigDecimal(charge.getDiscountAmount() == null ? BigDecimal.ZERO : charge.getDiscountAmount(), "0.00")));

        cell = row.createCell(c++);

        cell.setCellValue(Double.parseDouble(BigDecimals.formatBigDecimal(charge.getRefundAmount() == null ? BigDecimal.ZERO : charge.getRefundAmount(), "0.00")));

        cell = row.createCell(c++);
        if (charge.getBusType() == 2) {
            cell.setCellValue(refundWayMap.get(charge.getRefundWay().toString()));
        } else {
            cell.setCellValue(StringUtils.EMPTY);
        }


        cell = row.createCell(c++);
        cell.setCellValue(charge.getRegCampus() == null ? StringUtils.EMPTY : charge.getRegCampus());

        cell = row.createCell(c++);
        cell.setCellValue(charge.getChargeCampus() == null ? StringUtils.EMPTY : charge.getChargeCampus());

        cell = row.createCell(c++);
        cell.setCellValue(charge.getOperator() == null ? StringUtils.EMPTY : charge.getOperator());

        cell = row.createCell(c++);
        if (charge.getBusType() == 0) {
            cell.setCellValue(charge.getRegFrom() == 0 ? "线下" : charge.getRegFrom() == 1 ? "线上" : "转班");
        } else {
            cell.setCellValue(StringUtils.EMPTY);
        }

        cell = row.createCell(c++);
        cell.setCellValue(charge.getApplyRefundTime() == null ? StringUtils.EMPTY : Utils.formatDate(charge.getApplyRefundTime(), "yyyy-MM-dd HH:mm:ss"));

        cell = row.createCell(c++);
        cell.setCellValue(charge.getDailyStatus() == 0 ? "未日结" : "已日结");

        cell = row.createCell(c++);
        cell.setCellValue(charge.getClassName());

        cell = row.createCell(c++);
        cell.setCellValue(charge.getGradeName());

        cell = row.createCell(c++);
        cell.setCellValue(charge.getSubjectName());

        cell = row.createCell(c++);
        cell.setCellValue(charge.getCourseName());

        cell = row.createCell(c++);
        cell.setCellValue(charge.getProductName());

        cell = row.createCell(c++);
        cell.setCellValue(charge.getAreaTeamName());

        cell = row.createCell(c++);
        cell.setCellValue(charge.getBusTeamName());

        cell = row.createCell(c++);
        cell.setCellValue(charge.getRemark() == null ? StringUtils.EMPTY : charge.getRemark());

        cell = row.createCell(c++);
        cell.setCellValue(Utils.formatDate(charge.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));

        cell = row.createCell(c++);
        cell.setCellValue(charge.getPayChannelTransactionNo());
    }


    @ApiOperation("课程消耗统计")
    @RequestMapping(value = "/aggregateConsumeList", method = {RequestMethod.GET, RequestMethod.POST})
    @ResponseBody
    public ReturnResult<List<AggregationConsumeRecordVo>> aggregateConsumeList(ConsumeRecordQueryVo consumeRecordQueryVo, @RequestParam("startDate") Date startDate, @RequestParam("endDate") Date endDate, HttpServletRequest request) {
        DataPermission dataPermission = getDataPermission(request);
        List<AggregationConsumeRecordVo> list = consumeRecordService.aggregateConsume(consumeRecordQueryVo, startDate, endDate, dataPermission);
        return ReturnResult.success(list);
    }

    @ApiOperation("课程消耗统计汇总")
    @RequestMapping(value = "/findConsumeCollect", method = {RequestMethod.GET, RequestMethod.POST})
    @ResponseBody
    public ReturnResult<List<ConsumeCollectVo>> findConsumeCollect(@Valid @ModelAttribute ConsumeCollectQueryVo consumeCollectQueryVo, HttpServletRequest request) {
        DataPermission dataPermission = getDataPermission(request);
        List<ConsumeCollectVo> consumeCollect = consumeRecordService.findConsumeCollect(consumeCollectQueryVo, dataPermission);
        return ReturnResult.success(consumeCollect);
    }


    @ApiOperation("导出财务收支")
    @RequestMapping(value = "/exportConsumeAggregation", method = {RequestMethod.GET, RequestMethod.POST})
    public void exportConsumeAggregation(ConsumeRecordQueryVo consumeRecordQueryVo, @RequestParam("startDate") Date startDate, @RequestParam("endDate") Date endDate, HttpServletRequest request, HttpServletResponse response) throws IOException {

        DataPermission dataPermission = getDataPermission(request);
        List<AggregationConsumeRecordVo> list = consumeRecordService.aggregateConsume(consumeRecordQueryVo, startDate, endDate, dataPermission);

        SXSSFWorkbook excel = new SXSSFWorkbook();

        SXSSFSheet sheet = excel.createSheet("课程消耗");

        SXSSFRow firstRow = sheet.createRow(0);

        String[] titles = new String[]{"统计开始日期", "统计结束日期", "年份", "季节", "学生姓名", "学生编号", "报读校区", "就读年级", "报读班级", "报读班级编号", "任课老师", "年级", "科目", "消耗课时", "消耗金额", "红冲消耗", "差额消耗"};

        for (int i = 0; i < titles.length; i++) {

            SXSSFCell cell = firstRow.createCell(i);
            cell.setCellValue(titles[i]);

        }

        for (int i = 0; i < list.size(); i++) {

            SXSSFRow row = sheet.createRow(i + 1);

            AggregationConsumeRecordVo aggregationConsumeRecordVo = list.get(i);

            int c = 0;
            SXSSFCell cell = row.createCell(c++);
            cell.setCellValue(Utils.formatDate(aggregationConsumeRecordVo.getStartDate(), "yyyy-MM-dd"));

            cell = row.createCell(c++);
            cell.setCellValue(Utils.formatDate(aggregationConsumeRecordVo.getEndDate(), "yyyy-MM-dd"));

            cell = row.createCell(c++);
            cell.setCellValue(aggregationConsumeRecordVo.getYear());

            cell = row.createCell(c++);
            cell.setCellValue(aggregationConsumeRecordVo.getSeason());

            cell = row.createCell(c++);
            cell.setCellValue(aggregationConsumeRecordVo.getStudentName());

            cell = row.createCell(c++);
            cell.setCellValue(aggregationConsumeRecordVo.getStudentNo());

            cell = row.createCell(c++);
            cell.setCellValue(aggregationConsumeRecordVo.getCampusName());

            cell = row.createCell(c++);
            cell.setCellValue(aggregationConsumeRecordVo.getStudentGradeName());

            cell = row.createCell(c++);
            cell.setCellValue(aggregationConsumeRecordVo.getClassName());

            cell = row.createCell(c++);
            cell.setCellValue(aggregationConsumeRecordVo.getClassNo());

            cell = row.createCell(c++);
            cell.setCellValue(aggregationConsumeRecordVo.getTeacherName());

            cell = row.createCell(c++);
            cell.setCellValue(aggregationConsumeRecordVo.getGradeName());

            cell = row.createCell(c++);
            cell.setCellValue(aggregationConsumeRecordVo.getSubjectName());

            cell = row.createCell(c++);
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(aggregationConsumeRecordVo.getConsumeClassTimeOfThis());

            cell = row.createCell(c++);
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(Float.parseFloat(BigDecimals.formatBigDecimal(aggregationConsumeRecordVo.getConsumeAmountOfThis(), "0.00")));


            cell = row.createCell(c++);
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(Float.parseFloat(BigDecimals.formatBigDecimal(aggregationConsumeRecordVo.getRedAmountOfThis(), "0.00")));


            cell = row.createCell(c++);
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(Float.parseFloat(BigDecimals.formatBigDecimal(aggregationConsumeRecordVo.getDifferenceAmountOfThis(), "0.00")));

        }

        Webs.writeExcel(request, response, excel, "消耗统计");

    }

    @ApiOperation("支付流水列表")
    @RequestMapping(value = "/payRecordList", method = {RequestMethod.GET, RequestMethod.POST})
    @ResponseBody
    public ReturnResult<Page<PayRecord>> payRecordList(PayRecordQueryVo payRecordQueryVo, @RequestParam(value = "pageNo", defaultValue = "1") int pageNo,
                                                       @RequestParam(value = "pageSize", defaultValue = "20") int pageSize) {
        Page<NewPayRecord> page = newPayRecordService.findPayRecordByQuery(payRecordQueryVo, pageNo, pageSize);

        Page<PayRecordVo> nowPage = Utils.newPage(page);

        if (page.getRecords() != null) {
            List<PayRecordVo> list = toPayRecordVo(page.getRecords());
            nowPage.setRecords(list);
        }
        return ReturnResult.success(nowPage);
    }

    private List<PayRecordVo> toPayRecordVo(List<NewPayRecord> payRecordList) {
        if (payRecordList.isEmpty()) {
            return Collections.EMPTY_LIST;
        }
        List<Long> campusIds = BeanUtils.toList(payRecordList, "campusId");
        List<Campus> campusList = campusService.findByIds(campusIds);
        Map<Long, Campus> campusId2Campus = BeanUtils.toMap(campusList, "campusId");
        List<PayRecordVo> list = new ArrayList<>(payRecordList.size());
        for (NewPayRecord pr : payRecordList) {
            PayRecordVo vo = new PayRecordVo();
            BeanUtils.copyProperties(vo, pr);
            if (campusId2Campus.containsKey(pr.getCampusId())) {
                vo.setCampusName(campusId2Campus.get(pr.getCampusId()).getCampusName());
            }
            list.add(vo);
        }
        return list;
    }

    @ApiOperation("导出支付流水列表")
    @RequestMapping(value = "/exportPayRecordList", method = {RequestMethod.GET, RequestMethod.POST})
    public void exportPayRecordList(PayRecordQueryVo payRecordQueryVo, HttpServletResponse response, HttpServletRequest request) throws IOException {

        Page<NewPayRecord> page = newPayRecordService.findPayRecordByQuery(payRecordQueryVo, 1, 100000);

        List<PayRecordVo> payRecordVoList = toPayRecordVo(page.getRecords());

        SXSSFWorkbook excel = new SXSSFWorkbook();

        SXSSFSheet sheet = excel.createSheet("支付流水");

        SXSSFRow firstRow = sheet.createRow(0);


        String[] titles = new String[]{"课程订单号", "支付订单号", "post流水号", "支付状态", "支付类型", "支付方式", "支付金额", "支付时间", "商户号", "POS终端号", "收费校区", "操作人"};

        for (int i = 0; i < titles.length; i++) {
            SXSSFCell cell = firstRow.createCell(i);
            cell.setCellValue(titles[i]);
        }

        Map<String, String> payWayMap = new HashMap<>();
        payWayMap.put("0", "现金");
        payWayMap.put("1", "pos机");
        payWayMap.put("2", "转账");
        payWayMap.put("3", "账户");
        payWayMap.put("4", "在线支付");
        payWayMap.put("5", "预付金");

        Map<String, String> payWayMap2 = new HashMap<>();
        payWayMap2.put("VSP001", "银行卡");
        payWayMap2.put("VSP501", "微信支付");
        payWayMap2.put("VSP511", "支付宝支付");
        int rowNum=1;
        for (int i = 0; i < payRecordVoList.size(); i++) {



            PayRecordVo payRecordVo = payRecordVoList.get(i);
            if (StringUtils.isNotEmpty(payRecordVo.getPayWays())){
                List<JSONObject> payWays=JSONArray.parseArray(payRecordVo.getPayWays(),JSONObject.class);
                for (JSONObject jsonObject:payWays){
                    SXSSFRow row = sheet.createRow(rowNum++);
                    boolean isPost = jsonObject.getIntValue("payWay") == 1;
                    JSONObject payInfoExt = null;
                    if (jsonObject.getString("morePayInfo") != null) {
                        payInfoExt = JSONObject.parseObject(jsonObject.getString("morePayInfo"));
                    }


                    int c = 0;
                    SXSSFCell cell = row.createCell(c++);
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(String.valueOf(payRecordVo.getBusOrderId()));

                    cell = row.createCell(c++);
                    cell.setCellValue(isPost && payInfoExt != null ? payInfoExt.getString("bizseq") : StringUtils.EMPTY);

                    cell = row.createCell(c++);
                    cell.setCellValue(isPost && payInfoExt != null ? payInfoExt.getString("traceNo") : StringUtils.EMPTY);

                    cell = row.createCell(c++);
                    cell.setCellValue("支付成功");

                    cell = row.createCell(c++);
                    cell.setCellValue(payWayMap.get(jsonObject.getIntValue("payWay")+""));

                    cell = row.createCell(c++);
                    cell.setCellValue(isPost && payInfoExt != null ? payWayMap2.get(payInfoExt.getString("trxCode")) : StringUtils.EMPTY);

                    cell = row.createCell(c++);
                    cell.setCellValue(BigDecimals.formatBigDecimal(jsonObject.getBigDecimal("amount"), "0.00"));

                    cell = row.createCell(c++);
                    cell.setCellValue(Utils.formatDate(payRecordVo.getCreateTime(), "yyyy-MM-dd HH:mm:ss"));

                    cell = row.createCell(c++);
                    cell.setCellValue(isPost && payInfoExt != null ? payInfoExt.getString("cusId") : StringUtils.EMPTY);

                    cell = row.createCell(c++);
                    cell.setCellValue(isPost && payInfoExt != null ? payInfoExt.getString("termId") : StringUtils.EMPTY);

                    cell = row.createCell(c++);
                    cell.setCellValue(payRecordVo.getCampusName());

                    cell = row.createCell(c++);
                    cell.setCellValue(payRecordVo.getOperator());
                }
            }


        }

        Webs.writeExcel(request, response, excel, "支付流水");

    }


    @ApiOperation("新的消耗数据")
    @RequestMapping(value = "/consumeList", method = {RequestMethod.GET, RequestMethod.POST})
    @ResponseBody
    public ReturnResult<ConsumeRecordVo> consumeRecordList(HttpServletRequest request, StudentRegQueryVo2 studentRegQueryVo, @RequestParam(value = "pageNo", defaultValue = "1") int pageNo, @RequestParam(value = "pageSize", defaultValue = "20") int pageSize) {
        Page<ConsumeRecordVo> newPage = getConsumeRecordVoPage(request, studentRegQueryVo, pageNo, pageSize);
        return ReturnResult.success(newPage);
    }

    @ApiOperation("导出新的消耗数据")
    @RequestMapping(value = "/exportConsumeRecord", method = {RequestMethod.GET, RequestMethod.POST})
    public void exportConsumeRecord(StudentRegQueryVo2 studentRegQueryVo, HttpServletResponse response, HttpServletRequest request) throws IOException {
        Page<ConsumeRecordVo> newPage = getConsumeRecordVoPage(request, studentRegQueryVo, 1, 100000);
        List<ConsumeRecordVo> records = newPage.getRecords();

        SXSSFWorkbook excel = new SXSSFWorkbook();

        SXSSFSheet sheet = excel.createSheet("消耗统计（汇总）");


        SXSSFRow firstRow = sheet.createRow(0);


        String[] titles = new String[]{"年份", "期数", "学生姓名", "学生编号", "报读校区", "就读年级", "报读班级", "年级", "科目", "消耗课时", "消耗金额", "红冲消耗", "差额消耗"};

        for (int i = 0; i < titles.length; i++) {
            SXSSFCell cell = firstRow.createCell(i);
            cell.setCellValue(titles[i]);
        }
        int i = 1;
        for (ConsumeRecordVo record : records) {
            SXSSFRow row = sheet.createRow(i++);

            int c = 0;
            SXSSFCell cell = row.createCell(c++);
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(record.getYear());

            cell = row.createCell(c++);
            cell.setCellValue(record.getPeriodName());

            cell = row.createCell(c++);
            cell.setCellValue(record.getStudentName());

            cell = row.createCell(c++);
            cell.setCellValue(record.getStudentNo());

            cell = row.createCell(c++);
            cell.setCellValue(record.getCampusName());

            cell = row.createCell(c++);
            cell.setCellValue(record.getStudentGradeName());

            cell = row.createCell(c++);
            cell.setCellValue(record.getClassName());

            cell = row.createCell(c++);
            cell.setCellValue(record.getGradeName());

            cell = row.createCell(c++);
            cell.setCellValue(record.getSubjectName());

            cell = row.createCell(c++);
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(record.getConsumeClassTime());

            cell = row.createCell(c++);
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(record.getConsumeAmount().doubleValue());


            cell = row.createCell(c++);
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(record.getRedAmount().doubleValue());


            cell = row.createCell(c++);
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(record.getDifferenceAmount().doubleValue());


        }

        Webs.writeExcel(request, response, excel, "消耗统计（汇总）");

    }

    private Page<ConsumeRecordVo> getConsumeRecordVoPage(HttpServletRequest request, StudentRegQueryVo2 studentRegQueryVo, @RequestParam(value = "pageNo", defaultValue = "1") int pageNo, @RequestParam(value = "pageSize", defaultValue = "20") int pageSize) {
        DataPermission dataPermission = this.getDataPermission(request);
        studentRegQueryVo.setRegStatus(new Integer[]{0, 3});
        //studentRegQueryVo.setChargingStatus(new Integer[]{1,2});
        Page<StudentRegDetail> page = studentRegDetailService.findStudentRegByPermissionQuery2(dataPermission, studentRegQueryVo, pageNo, pageSize);

        List<StudentRegDetail> records = page.getRecords();
        List<ConsumeRecordVo> recordVos = Lists.transform(records, (src) -> {
            ConsumeRecordVo consumeRecordVo = new ConsumeRecordVo();
            consumeRecordVo.setClassName(src.getClassName());
            consumeRecordVo.setBusTeamName(src.getBusTeamName());
            consumeRecordVo.setGradeName(src.getGradeName());
            consumeRecordVo.setSubjectName(src.getSubjectName());
            consumeRecordVo.setPeriodName(src.getPeriodName());
            consumeRecordVo.setCampusName(src.getCampusName());
            consumeRecordVo.setStudentName(src.getStudentName());
            consumeRecordVo.setStudentNo(src.getStudentNo());
            consumeRecordVo.setStudentGradeName(src.getStudentGradeName());
            consumeRecordVo.setConsumeClassTime(src.getRegStatus() == 0 ? (src.getEndAmount() - src.getStartAmount() + 1) * 2 : 0);
            consumeRecordVo.setYear(src.getPeriodName().substring(0, 4));
            consumeRecordVo.setConsumeAmount(src.getPayAmount());
            consumeRecordVo.setRedAmount(src.getRefundAmount().multiply(BigDecimal.valueOf(-1)));
            consumeRecordVo.setDifferenceAmount(src.getRegStatus() == 3 ? src.getPayAmount().subtract(src.getRefundAmount()) : BigDecimal.ZERO);
            return consumeRecordVo;
        });

        Page<ConsumeRecordVo> newPage = Utils.newPage(page);
        newPage.setRecords(recordVos);
        return newPage;
    }

    @ApiOperation("营收预测")
    @RequestMapping(value = "/countClassPreIncome", method = {RequestMethod.GET, RequestMethod.POST})
    @ResponseBody
    public ReturnResult<ClassPreIncomeDetailVo> countClassPreIncome(@ModelAttribute ClassPreIncomeDetailQueryVo classPreIncomeDetailQueryVo) {
        List<ClassPreIncomeDetailVo> classPreIncomeDetailVos = mergeStudentRegDetailService.findClassPreIncomeDetailVo(classPreIncomeDetailQueryVo);
        return ReturnResult.success(classPreIncomeDetailVos);
    }

    @ApiOperation("导出营收预测")
    @RequestMapping(value = "/exportcountClassPreIncome", method = {RequestMethod.GET, RequestMethod.POST})
    public void countClassPreIncome(@ModelAttribute ClassPreIncomeDetailQueryVo classPreIncomeDetailQueryVo, HttpServletResponse response, HttpServletRequest request) throws IOException {
        List<ClassPreIncomeDetailVo> classPreIncomeDetailVos = mergeStudentRegDetailService.findClassPreIncomeDetailVo(classPreIncomeDetailQueryVo);

        SXSSFWorkbook excel = new SXSSFWorkbook();

        SXSSFSheet sheet = excel.createSheet("营收预测");


        SXSSFRow firstRow = sheet.createRow(0);


        String[] titles = new String[]{"统计开始时间", "统计结束时间", "区域", "业务组", "期数", "年级", "科目", "校区", "班级编号", "班级名称", "已缴金额", "班级总讲次", "上课讲次", "营收"};

        for (int i = 0; i < titles.length; i++) {
            SXSSFCell cell = firstRow.createCell(i);
            cell.setCellValue(titles[i]);
        }
        int i = 1;
        for (ClassPreIncomeDetailVo record : classPreIncomeDetailVos) {
            SXSSFRow row = sheet.createRow(i++);

            int c = 0;
            SXSSFCell cell = row.createCell(c++);
            cell.setCellValue(record.getCountStartTime());

            cell = row.createCell(c++);
            cell.setCellValue(record.getCountEndTime());

            cell = row.createCell(c++);
            cell.setCellValue(record.getAreaTeamName());

            cell = row.createCell(c++);
            cell.setCellValue(record.getBusTeamName());

            cell = row.createCell(c++);
            cell.setCellValue(record.getPeriodName());

            cell = row.createCell(c++);
            cell.setCellValue(record.getGradeName());

            cell = row.createCell(c++);
            cell.setCellValue(record.getSubjectName());

            cell = row.createCell(c++);
            cell.setCellValue(record.getCampusName());

            cell = row.createCell(c++);
            String classId = null != record.getClassId() ? record.getClassId().toString() : "";
            cell.setCellValue(classId);

            cell = row.createCell(c++);
            cell.setCellValue(record.getClassName());

            cell = row.createCell(c++);
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(record.getRealAmount().doubleValue());


            cell = row.createCell(c++);
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(record.getLectureAmount());


            cell = row.createCell(c++);
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(record.getAttendLectureAmount());

            cell = row.createCell(c++);
            cell.setCellType(CellType.NUMERIC);
            cell.setCellValue(record.getIncome().doubleValue());

        }

        Webs.writeExcel(request, response, excel, "营收预测");

    }

}
